home *** CD-ROM | disk | FTP | other *** search
/ Programming Languages Suite / ProgramD2.iso / Visual Database / Visual Foxpro 6.0 (Ent. Edition) / Vf6ent Extractor.EXE / TOOLS / XSOURCE / XSOURCE.ZIP / vfpsource / wizards / Wztable / wztbleng.prg < prev    next >
Encoding:
Text File  |  1998-05-01  |  9.9 KB  |  298 lines
  1. * Table wizard engine
  2.  
  3. #INCLUDE wztable.h
  4.  
  5. DEFINE CLASS TableEngine AS WizEngineAll
  6.     lInitValue = .t.
  7.     iHelpContextID = 1895825424
  8.  
  9.     PROCEDURE ProcessOutput
  10.  
  11.         * ---------------------------------------------------------------------------------
  12.         * Create the table and index here. 
  13.         * ---------------------------------------------------------------------------------
  14.  
  15.         private wzserrorig, fldcount, fpos, wzi, tagname, tagexpr, wzlOKcreate, m.wzsTablename, m.wzalias, ;
  16.             m.lHaveDBC
  17.         LOCAL MaxKeyLen, IndexExpr, cTableFriendlyName, lHaveRels, i, j
  18.         LOCAL cKey, cRefTable, cRefPrimaryKey, cNewTableRef
  19.         LOCAL cNewFldType, cNewFldLen, cNewFldDec, cNewFldNull, cFldChar
  20.  
  21.         IF oWizard.lCreateDBC
  22.             *- create DBC, add table to it
  23.             CREATE DATABASE (oWizard.cDBCName)
  24.         ENDIF
  25.         
  26.         m.lHaveDBC = oWizard.HaveDBC            && okay to add to shared DBC
  27.         m.cNewDBC = ""
  28.         
  29.         lHaveRels = !EMPTY(wzat_rels[1,1])
  30.         
  31.         SELECT crsFields
  32.         COUNT FOR NOT DELETED() TO m.fldcount
  33.         DIMENSION wzaStruct[m.fldcount,6]
  34.         DIMENSION wzaExtra[m.fldcount,5]        && 1 - actual field name
  35.                                                 && 2 - long field name (caption)
  36.                                                 && 3 - .T. if indexed
  37.                                                 && 4 - input mask
  38.                                                 && 5 - format
  39.         m.wzi = 0
  40.         m.fldstr = ""
  41.  
  42.         SCAN FOR NOT DELETED()
  43.             m.wzi = m.wzi + 1
  44.             wzaStruct[m.wzi,1] = alltrim(fname)
  45.             wzaStruct[m.wzi,2] = alltrim(ftype)
  46.             wzaStruct[m.wzi,3] = flen
  47.             wzaStruct[m.wzi,4] = fdec
  48.             wzaStruct[m.wzi,5] = fnull
  49.             wzaStruct[m.wzi,6] = lNoCPTrans
  50.             
  51.             * Truncate to 10 if necessary, if a DBC is not involved. Check for duplicate field names and 
  52.             * automatically rename this field to a unique name, using numbers at the end, e.g. "CUSTOMERNAME"
  53.             * shortens to "CUSTOMERNA", and if this is not unique, it gets renamed to "CUSTOMERN1", "CUSTOMERN2"...
  54.             * "CUSTOM9999", until a unique name is found.
  55.             
  56.             IF NOT m.lHaveDBC AND LEN(wzaStruct[m.wzi,1]) > 10
  57.                 wzaStruct[m.wzi,1] = LEFT(wzaStruct[m.wzi,1], 10)
  58.                 IF " " + wzaStruct[m.wzi,1] + " " $ m.fldstr
  59.                     FOR m.wzi2 = 1 TO 10000
  60.                         m.dupestr = LTRIM(STR(m.wzi2))
  61.                         wzaStruct[m.wzi,1] = LEFT(wzaStruct[m.wzi,1],10-LEN(m.dupestr)) + m.dupestr
  62.                         IF NOT " " + wzaStruct[m.wzi,1] + " " $ m.fldstr
  63.                             EXIT
  64.                         ENDIF
  65.                     ENDFOR
  66.                 ENDIF
  67.             ENDIF
  68.             
  69.             m.fldstr = " " + wzaStruct[m.wzi,1] + " "
  70.             wzaExtra[m.wzi,1] = wzaStruct[m.wzi,1]
  71.             wzaExtra[m.wzi,2] = ALLTRIM(fieldname)
  72.             wzaExtra[m.wzi,4] = ALLTRIM(fmask)
  73.             wzaExtra[m.wzi,5] = ALLTRIM(fformat)
  74.             IF MakeTag
  75.                 wzaExtra[m.wzi,3] = .t.
  76.             ENDIF
  77.         ENDSCAN
  78.  
  79.         m.cShortname = IIF("\" $ oWizard.cTableName, SUBSTR(oWizard.cTableName,RAT("\",oWizard.cTableName)+1), ;
  80.                              oWizard.cTableName)
  81.         m.cShortname = UPPER(IIF("." $ m.cShortname, LEFT(m.cShortname, AT(".",m.cShortname)-1), m.cShortname))
  82.  
  83.         IF oWizard.DBCFlag = 2                                && table exists in dbc, user wants to overwrite it
  84.             IF ADBOBJ(wzatemp,"TABLE") > 0 AND ASCAN(wzatemp, m.cShortname) > 0
  85.                 REMOVE TABLE (m.cShortname)
  86.             ENDIF
  87.         ENDIF
  88.                     
  89.         m.wzserrorig = on("error")
  90.         on error do THISFORMSET.ALERT(C_TBLERR_LOC)
  91.  
  92.         m.wzsTablename = oWizard.cTableName
  93.         m.cTableFriendlyName = LEFT(ALLTRIM(oWizard.cTableFriendlyName),128)
  94.         DO CASE
  95.             CASE !m.lHaveDBC
  96.                 CREATE TABLE (m.wzsTablename) FREE FROM ARRAY wzaStruct 
  97.             CASE !EMPTY(oWizard.cTableFriendlyName)
  98.                 CREATE TABLE (m.wzsTablename) NAME "&cTableFriendlyName" FROM ARRAY wzaStruct
  99.             OTHERWISE
  100.                 CREATE TABLE (m.wzsTablename) FROM ARRAY wzaStruct
  101.         ENDCASE
  102.         cNewTableRef = IIF(!EMPTY(m.cTableFriendlyName),STRTRAN(m.cTableFriendlyName," ","_"),m.cShortname)
  103.         
  104.         *- reopen table, and give it a safe alias
  105.         IF USED("_newtable")
  106.             USE IN _newtable
  107.         ENDIF
  108.         USE (m.wzsTablename) ALIAS _newtable
  109.         
  110.         on error &wzserrorig
  111.  
  112.         m.wzlOKcreate = (DBF() = oWizard.cTableName)
  113.         m.wzalias = ALIAS()
  114.  
  115.         IF m.wzlOKcreate
  116.  
  117.             oEngine.cOutfile = oWizard.cTableName
  118.             m.cOutputFilename = oWizard.cTableName
  119.  
  120.             *- build indexes and update DBC captions
  121.             this.HadError = .f.
  122.             this.SetErrorOff = .t.
  123.             SELECT (m.wzalias)
  124.               FOR m.wzi = 1 TO m.fldcount
  125.                   m.thisfld = wzaExtra[m.wzi,1]
  126.                 m.tagname = IIF(LEN(m.thisfld) > 10, LEFT(m.thisfld,10), m.thisfld)
  127.                   IF wzaExtra[m.wzi,3]
  128.                       *- create an index
  129.                       m.MaxKeyLen = IIF(SET("collate") == "MACHINE",240,120) - IIF(wzaStruct[m.wzi,5],1,0)
  130.                       IF wzaStruct[m.wzi,2] = "C" AND wzaStruct[m.wzi,3] > m.MaxKeyLen
  131.                           IndexExpr = "LEFT(" + thisfld + "," + ALLTRIM(STR(m.MaxKeyLen)) + ")"
  132.                       ELSE
  133.                         IndexExpr = thisfld
  134.                     ENDIF
  135.                       IF wzaExtra[m.wzi,1] = oWizard.Keyfield
  136.                           ALTER TABLE (m.wzsTablename) ADD PRIMARY KEY &indexexpr TAG &tagname
  137.                       ELSE
  138.                         *- create the index the old way
  139.                         INDEX ON &indexexpr TAG &tagname
  140.                     ENDIF                    
  141.                 ENDIF
  142.                 IF m.lHaveDBC
  143.                       *- check to see if we need to create a relation on this field
  144.                     *- wzat_rels[n,7]:
  145.                     *- col 1: description that shows in combobox
  146.                     *- col 2: name of other table in DBC 
  147.                     *- col 3: type of relation (1 = none; 2 = 1-many; 3 = many-1
  148.                     *- col 4: the primary key in the parent table
  149.                     *- col 5: tag in child table
  150.                     *- col 6: key expression in child table
  151.                     *- col 7: possible new key field to add
  152.                     IF m.lHaveRels
  153.                         FOR m.i = 1 TO ALEN(wzat_rels,1)
  154.                             IF wzat_rels[i,5] == C_NEWTAG_LOC
  155.                                 LOOP
  156.                             ENDIF
  157.                             DO CASE
  158.                                 CASE wzat_rels[m.i, 3] == 2
  159.                                     *- need to create a 1-many link on this field from primary key
  160.                                     *- of link-to table
  161.                                     IF wzaExtra[m.wzi,1] = oWizard.Keyfield    && LOWER(wzat_rels[m.i,6]) == LOWER(indexexpr)
  162.                                         IF USED("_child")
  163.                                             USE IN _child
  164.                                         ENDIF
  165.                                         tagname = wzat_rels[m.i,4]
  166.                                         cRefTable = wzat_rels[m.i, 2]
  167.                                         USE (cRefTable) ALIAS _child IN 0 AGAIN
  168.                                         cKey = UPPER(wzat_rels[m.i,6])
  169.                                         ALTER TABLE _child ;
  170.                                             ADD FOREIGN KEY ;
  171.                                             TAG &cKey ;
  172.                                             REFERENCES &cNewTableRef ;
  173.                                             TAG &tagname
  174.                                         USE IN _child
  175.                                     ENDIF
  176.                                 CASE wzat_rels[m.i, 3] == 3
  177.                                     *- need to create a 1-many link on this field from primary key
  178.                                     *- of link-to table
  179.                                     IF LOWER(wzat_rels[m.i,5]) == LOWER(tagname)
  180.                                         cRefTable = wzat_rels[m.i, 2]
  181.                                         cKey = wzaStruct[m.wzi,1]
  182.                                         ALTER TABLE (m.wzsTablename) ;
  183.                                             ADD FOREIGN KEY ;
  184.                                             TAG &cKey ;
  185.                                             REFERENCES &cRefTable;
  186.                                             TAG &tagname
  187.                                     ENDIF
  188.                             ENDCASE
  189.                         NEXT
  190.                     ENDIF        && m.lHaveRels
  191.                     =DBSETPROP(m.cNewTableRef + "." + wzaExtra[m.wzi,1],"FIELD","CAPTION",wzaExtra[m.wzi,2])
  192.                     =DBSETPROP(m.cNewTableRef + "." + wzaExtra[m.wzi,1],"FIELD","INPUTMASK",wzaExtra[m.wzi,4])
  193.                     =DBSETPROP(m.cNewTableRef + "." + wzaExtra[m.wzi,1],"FIELD","FORMAT",wzaExtra[m.wzi,5])
  194.                 ENDIF
  195.                 IF THIS.HadError
  196.                     EXIT
  197.                 ENDIF
  198.             ENDFOR
  199.             *- see if user wants to add any new link fields
  200.             IF m.lHaveDBC AND m.lHaveRels
  201.                 FOR m.i = 1 TO ALEN(wzat_rels,1)
  202.                     IF wzat_rels[i,5] == C_NEWTAG_LOC AND !EMPTY(wzat_rels[i,7])
  203.                         *- user wants to add a new field to the child table
  204.                         IF USED("_child")
  205.                             USE IN _child
  206.                         ENDIF
  207.                         tagname = wzat_rels[m.i,7]
  208.                         cRefTable = wzat_rels[m.i, 2]
  209.                         IF USED(cRefTable)
  210.                             USE IN (cRefTable)
  211.                         ENDIF
  212.                         USE (cRefTable) ALIAS _child IN 0
  213.                         DO CASE
  214.                             CASE wzat_rels[m.i, 3] == 2
  215.                                 *- other table is child
  216.                                 *- figure out the type and length of the new table primary key
  217.                                 j = oEngine.AColScan(@wzaExtra, oWizard.Keyfield,1,.T.)
  218.                                 IF j == 0
  219.                                     *- error -- unable to locate the primary key
  220.                                     EXIT
  221.                                 ELSE
  222.                                     cNewFldType = wzaStruct[j, 2]
  223.                                     cNewFldLen = wzaStruct[j, 3]
  224.                                     cNewFldDec = wzaStruct[j, 4]
  225.                                     cNewFldNull = IIF(wzaStruct[j, 5], "NULL", "NOT NULL")
  226.                                     cNewCPTrans = IIF(wzaStruct[j, 6], "NOCPTRANS", "")
  227.                                     cFldChar = cNewFldType + " (" + LTRIM(STR(cNewFldLen)) + "," + LTRIM(STR(cNewFldDec)) + ") " + ;
  228.                                         cNewFldNull + cNewCPTrans
  229.                                 ENDIF
  230.                                 m.cRefPrimaryKey = DBGETPROP(m.cShortname,"Table","PrimaryKey")
  231.                                 IF EMPTY(m.cRefPrimaryKey)
  232.                                     *- error -- unable to locate the primary key
  233.                                     EXIT
  234.                                 ENDIF
  235.                                 cKey = wzat_rels[m.i,7]
  236.                                 ALTER TABLE (m.cRefTable) ;
  237.                                     ADD COLUMN &cKey &cFldChar ;
  238.                                     REFERENCES &cNewTableRef ;
  239.                                     TAG &cRefPrimaryKey
  240.                                     
  241.                                 #IF 0
  242.                                     ADD FOREIGN KEY
  243.                                     TAG &cKey
  244.                                 #ENDIF
  245.                                 
  246.                             CASE wzat_rels[m.i, 3] == 3
  247.                                 *- new table is child
  248.                                 *- figure out the type and length of the linking table primary key
  249.                                 m.cRefPrimaryKey = DBGETPROP(m.cRefTable,"Table","PrimaryKey")
  250.                                 IF EMPTY(m.cRefPrimaryKey)
  251.                                     *- error -- unable to locate the primary key
  252.                                     EXIT
  253.                                 ENDIF
  254.                                 DIMENSION aChild(1,1)
  255.                                 iAChildLen = AFIELDS(aChild,"_child")
  256.                                 j = oEngine.AColScan(@aChild, UPPER(m.cRefPrimaryKey),1,.T.)
  257.                                 IF m.j == 0
  258.                                     *- error -- unable to locate the primary key
  259.                                     EXIT
  260.                                 ELSE
  261.                                     cNewFldType = aChild[j, 2]
  262.                                     cNewFldLen = aChild[j, 3]
  263.                                     cNewFldDec = aChild[j, 4]
  264.                                     cNewFldNull = IIF(aChild[j, 5], "NULL", "NOT NULL")
  265.                                     cNewCPTrans = IIF(aChild[j, 6], "NOCPTRANS", "")
  266.                                     cFldChar = cNewFldType + " (" + LTRIM(STR(cNewFldLen)) + "," + LTRIM(STR(cNewFldDec)) + ") " + ;
  267.                                         cNewFldNull + cNewCPTrans
  268.                                 ENDIF
  269.                                 cKey = wzat_rels[m.i,7]
  270.                                 ALTER TABLE (m.wzsTablename) ;
  271.                                     ADD COLUMN &cKey &cFldChar ;
  272.                                     REFERENCES &cRefTable;
  273.                                     TAG &cRefPrimaryKey
  274.                                     
  275.                                 *-    ADD FOREIGN KEY
  276.                                 *-    TAG &cKey
  277.                         ENDCASE
  278.                         USE IN _child
  279.                     ENDIF
  280.                 NEXT
  281.             ENDIF
  282.             
  283.             
  284.             USE IN (m.wzalias)
  285.               SELECT crsFields
  286.               
  287.             IF THIS.HadError
  288.                 THIS.Alert(C_IDXERR_LOC)
  289.                 THIS.SetErrorOff = .F.
  290.                 THIS.HadError = .F.
  291.             ENDIF
  292.             
  293.         ENDIF
  294.         
  295.     ENDPROC
  296.  
  297. ENDDEFINE
  298.